package com.b511.swpu.cementbackend.repository;

import com.b511.swpu.cementbackend.dto.output.WellinfoDto;
import com.b511.swpu.cementbackend.entity.CM_WorkBaseInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface CompareTreeRepository extends JpaRepository<CM_WorkBaseInfo, String>, JpaSpecificationExecutor<CM_WorkBaseInfo> {

    @Query(value = "select distinct country from CM_WORK_BASE_INFO",nativeQuery = true)//nativeQuery为true表示使用原生SQl
    List<String> findCountryByDistinct();

    @Query(value = "select distinct area from CM_WORK_BASE_INFO where country=:country",nativeQuery = true)
    List<String> findAreaByDistinct(@Param("country") String country);

    @Query(value = "select distinct block from CM_WORK_BASE_INFO where country=:country and area=:area",nativeQuery = true)//nativeQuery为true表示使用原生SQl
    List<String> findBlockByDistinct(@Param("country") String country, @Param("area") String area);

    @Query(value = "select distinct oilField from CM_WORK_BASE_INFO where country=:country and area=:area and block=:block",nativeQuery = true)
    List<String> findOilFieldByDistinct(@Param("country") String country, @Param("area") String area, @Param("block") String block);

    @Query(value = "select distinct wellName from CM_WORK_BASE_INFO where country=:country and area=:area and block=:block and oilfield=:oilField",nativeQuery = true)
    List<String> findWellNameByDistinct(@Param("country") String country, @Param("area") String area, @Param("block") String block, @Param("oilField") String oilField);

    @Query(value = "select distinct casingSize from CM_WORK_BASE_INFO where country=:country and area=:area and block=:block and oilfield=:oilField  and wellName=:wellName",nativeQuery = true)
    List<String> findCasingSizeByDistinct(@Param("country") String country, @Param("area") String area, @Param("block") String block, @Param("oilField") String oilField,@Param("wellName") String wellName);



    //由于这里使用的是HQL，所以相应的字段应与实体类对应。
    @Query(value = "select new com.b511.swpu.cementbackend.dto.output.WellinfoDto(CM_WorkBaseInfo_ID,casingSize) FROM CM_WorkBaseInfo where country=:country and area=:area and block=:block and oilfield=:oilField  and wellName=:wellName and casingSize=:casingSize")
    WellinfoDto findIt(@Param("country") String country, @Param("area") String area, @Param("block") String block, @Param("oilField") String oilField, @Param("wellName") String wellName, @Param("casingSize") String casingSize);

}
